Oracle 锁表排查及解锁 SQL
本文档记录了 Oracle 数据库中锁表排查及解锁的常用 SQL 语句,涵盖临时表锁处理、对象锁查询及 Kill 会话等场景。
一、处理 ORA-14450 临时表被占用并解锁
1.1 查询持有临时表锁的会话
SELECT SID, SERIAL# FROM V$session WHERE SID IN (SELECT sid FROM v$enqueue_lock t WHERE t.type = 'TO');
1.2 生成 Kill 会话语句
SELECT 'alter system kill session ''' || a.sid || ',' || a.SERIAL# || ''';'
FROM V$session a
WHERE SID IN (SELECT SID FROM v$enqueue_lock t WHERE t.type = 'TO');
二、查询锁表并生成解锁语句
2.1 按表名查询锁表会话
SELECT 'alter system kill session ''' || a.sid || ',' || c.serial# || ''';'
FROM v$lock a, dba_objects b, v$session c
WHERE a.id1 = b.object_id
AND a.sid = c.sid
AND b.object_name = UPPER('ORDERRETAIL_RP_RETAIL004');
三、解决 ORA-14450:试图访问已经在使用的事务处理临时表
3.1 查询对象信息
SELECT * FROM dba_objects WHERE object_name = 'RP_STORE_DAILYBALANCE_GEN';
3.2 按对象 ID 查询锁并生成解锁语句
SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';'
FROM v$session
WHERE sid IN (SELECT sid
FROM v$enqueue_lock t
WHERE t.type = 'TO'
AND ID1 = '132733');
3.3 通用:解决 ORA-14450 临时表被占用
SELECT 'alter system kill session ''' || a.sid || ',' || a.SERIAL# || ''';'
FROM V$session a
WHERE SID IN (SELECT SID FROM v$enqueue_lock t WHERE t.type = 'TO');